import sqlite3

# 查看表名
selectAll = "SELECT tbl_name FROM sqlite_master WHERE type = 'table'"
# 获取字段名
selectParrten = "SELECT sql FROM sqlite_master WHERE type = 'table' AND tbl_name = 'ipArp_table';"
# 创建表
sql_create = """
            create table ipArp_table(
                    id varchar(4) primary key,
                    ip varchar(20),
                    mac varchar(20)
            )
"""
# 插入数据
insertSql = "insert into ipArp_table (ip, mac) VALUES ( ?, ?)" \
    # 查询数据
selectSql = '''select * from ipArp_table'''

# 删除数据
deleteSql = "delete from ipArp_table"

# 查询数据通过Ip
selectByIpSql = 'select * from ipArp_table where ip = ?'

# 查询不包含某数据



def createTable(databaseConnection):
    cur = databaseConnection.cursor()
    cur.execute(sql_create)
    cur.close()


def insertDate(date, databaseConnection):
    cur = databaseConnection.cursor()
    cur.execute(insertSql, date)
    databaseConnection.commit()
    cur.close()


def selectDate(databaseConnection):
    cur = databaseConnection.cursor()
    res = cur.execute(selectSql)
    data = res.fetchall()
    cur.close()
    return data


def deleteDate(databaseConnection):
    cur = databaseConnection.cursor()
    cur.execute(deleteSql)
    databaseConnection.commit()
    cur.close()


def searchDataByIp(databaseConnection, ip):
    cur = databaseConnection.cursor()
    res = cur.execute(selectByIpSql, (ip,))
    res = res.fetchall()
    cur.close()
    return res


def selectDataUnContain(databaseConnection, ip):
    cur = databaseConnection.cursor()
    print(ip)
    selectUnContainSql = 'select * from ipArp_table where REGEXP('+ '{ip}' + ', ip);'
    res = cur.execute(selectUnContainSql)
    res = res.fetchall()
    cur.close()
    return res




if __name__ == "__main__":
    # 创建连接
    con = sqlite3.connect('../DataBase/ipArpTable.db')
    # createTable(con)
    # insertDate((3,'1231','1231231'),con)
    # con.commit()
    # res = selectDate(con)
    # print(res)
